package org.homly.sql;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class SQLTool {

	public static Connection getConnection() throws Exception {

		Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
		return DriverManager.getConnection("proxool.xml-db");
	}

	public static void closeResource(Connection conn, PreparedStatement pstmt, ResultSet rs) {

		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (pstmt != null ) {
				pstmt.close();
				pstmt = null;
			}
			if (conn != null && !conn.isClosed()) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}				
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//检查SQL查询后是否有结果
	public static boolean check(String sql) throws Exception {

		boolean flag = false;

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = SQLTool.getConnection();
			pstmt = conn.prepareStatement(sql);
			System.out.println("SQL: " + sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				flag = true;
			} else {
				flag = false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SQLTool.closeResource(conn, pstmt, rs);
		}

		return flag;
	}

	//查询语句
	public static List<HashMap<String, String>> query(String sql) throws Exception {

		List<HashMap<String, String>> resultList = new ArrayList<HashMap<String, String>>();

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = SQLTool.getConnection();
			pstmt = conn.prepareStatement(sql);
			System.out.println("SQL: " + sql);
			rs = pstmt.executeQuery();

			ResultSetMetaData meta = rs.getMetaData();
			String columnLabel = null;
			String columnValue = null;
			while (rs.next()) {
				HashMap<String, String> hm = new HashMap<String, String>();
				for (int i = 1; i <= meta.getColumnCount(); i ++) {
					columnLabel = meta.getColumnLabel(i);
					columnValue = (rs.getString(i) == null) ? "" : rs.getString(i);
					hm.put(columnLabel, columnValue);
				}
				resultList.add(hm);
				hm = null;
			}
			columnValue = null;
			columnLabel = null;
			meta = null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SQLTool.closeResource(conn, pstmt, rs);
		}

		return resultList;
	}

	/*
	 * 查询语句（分页）
	 * pageColumn——分页的字段依据
	 * pageMax——单页数量
	 * pageNum——本页页数
	 */
	public static List<HashMap<String, String>> query(String selectS, String fromS, String whereS, String orderS,
			String pageColumn, int pageMax, int pageNum) throws Exception {

		//临时表的表名，默认为pageColumn包含的表名
		String tmpTableName = null;
		Integer pageColumnDotIndex = pageColumn.indexOf(".");
		if (pageColumnDotIndex != null && pageColumnDotIndex.intValue() > -1) {
			tmpTableName = pageColumn.substring(0, pageColumnDotIndex);
		}
		if (tmpTableName == null || "".equals(tmpTableName)) {
			tmpTableName = "tmp_t";
		}
		//临时表的提取字段，默认为orderS的字段+pageColumn
		String tmpColumn = null;
		if (orderS != null && !"".equals(orderS)) {
			tmpColumn = orderS.trim().toLowerCase().replace("order by ", "")
					.replace(" asc", "").replace(" desc", "");
			if (!tmpColumn.contains(pageColumn)) {
				tmpColumn += "," + pageColumn;
			}
		} else {
			tmpColumn = pageColumn;
		}

		//notInSqlSB和inSqlSB结合起来，查询出本页的pageColumn的值
		StringBuffer notInSqlSB = new StringBuffer();
		notInSqlSB
				.append(" SELECT ").append(pageColumn)
				.append(" FROM")
						.append(" (")
							.append(" SELECT DISTINCT ")
									.append(tmpColumn)
							.append(fromS)
							.append(whereS)
							.append(orderS)
							.append(" LIMIT ").append(pageMax * (pageNum - 1))
						.append(" ) AS ").append(tmpTableName);
		StringBuffer inSqlSB = new StringBuffer();
		inSqlSB
				.append(" SELECT ").append(pageColumn)
				.append(" FROM")
						.append(" (")
							.append(" SELECT DISTINCT ").append(tmpColumn).append(" ")
							.append(fromS)
							.append(whereS).append(" AND ").append(pageColumn).append(" NOT IN ")
									.append(" (").append(notInSqlSB).append(" )")
							.append(orderS)
							.append(" LIMIT ").append(pageMax)
						.append(" ) AS ").append(tmpTableName);
		notInSqlSB = null;

		StringBuffer sqlSB = new StringBuffer();
		sqlSB
				.append(selectS)
				.append(fromS)
				.append(whereS).append(" AND ").append(pageColumn).append(" IN")
					.append(" (").append(inSqlSB).append(" )")
				.append(orderS);
		inSqlSB = null;

		List<HashMap<String, String>> resultList = query(sqlSB.toString());

		return resultList;
	}

	//更新数据库
	public static int update(String sql, Object[] para) throws Exception {

		int flag = 1;

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = SQLTool.getConnection();
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < para.length; i ++) {
				if (para[i] instanceof Integer) {
					pstmt.setInt((i + 1), ((Integer) para[i]).intValue());
				} else if (para[i] instanceof String) {
					pstmt.setString((i + 1), para[i].toString());
				} else if (para[i] instanceof Date) {
					Date utilDate = (Date) para[i];
					pstmt.setTimestamp((i + 1), new Timestamp(utilDate.getTime()));
				} else if (para[i] instanceof Short) {
					pstmt.setShort((i + 1), (Short) para[i]);
				} else if (para[i] instanceof Double) {
					pstmt.setDouble((i + 1), (Double) para[i]);
				} else {
					pstmt.setObject((i + 1), para[i]);
				}
			}
			System.out.println("SQL: " + sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();

			flag = 0;
		} finally {
			SQLTool.closeResource(conn, pstmt, rs);
		}

		return flag;
	}

	//插入数据库
	public static int insert(String sql, Object[] para) throws Exception {

		int flag = SQLTool.update(sql, para);

		return flag;
	}
}
